//
//  Sqlite3Utils.cpp
//  dragonWorld
//
//  Created by yanglishuan on 13-9-3.
//
//

#include "Sqlite3Utils.h"
#include <string>

Sqlite3Utils::Sqlite3Utils(const char *path)
{
    std::cout << "Sqlite3Utils create ..." << std::endl;
    dbPath = path;
    this->column_names = cocos2d::CCArray::create();
}

Sqlite3Utils::~Sqlite3Utils()
{
    std::cout << "Sqlite3Utils destroy ..." << std::endl;
}


int Sqlite3Utils::open_sqlite()
{
    int rc = sqlite3_open_v2(dbPath.getCString(), &sqlite3_db, SQLITE_OPEN_READWRITE, NULL);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(sqlite3_db));
    }
    return rc;
}

int Sqlite3Utils::close_sqlite()
{
    int rc = sqlite3_close(sqlite3_db);
    if (rc != SQLITE_OK) {
        fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(sqlite3_db));
    }
    return rc;
}

cocos2d::CCArray* Sqlite3Utils::results(const char *tableName, const char *condition, const char *order, bool asc)
{
    cocos2d::CCArray *retArr = cocos2d::CCArray::create();
    ;
    cocos2d::CCString *sql;

    sql = cocos2d::CCString::createWithFormat("select * from %s", tableName);
    if (condition == NULL || strlen(condition) == 0) {
        sql = cocos2d::CCString::createWithFormat("%s", sql->getCString());
    } else {
        sql = cocos2d::CCString::createWithFormat("%s where %s", sql->getCString(), condition);
    }
    
    if (order == NULL || strlen(order) == 0) {
        sql = cocos2d::CCString::createWithFormat("%s;", sql->getCString());
    } else {
        sql = cocos2d::CCString::createWithFormat("%s order by %s", sql->getCString(), order);
        if (asc) {
            sql = cocos2d::CCString::createWithFormat("%s asc;", sql->getCString());
        } else {
            sql = cocos2d::CCString::createWithFormat("%s desc;", sql->getCString());
        }
    }
    
    std::cout << "要查找的sql语句为 = " << sql->getCString() << std::endl;
    
    sqlite3_stmt *stmt;
    const char *tail;
    
    int rc = sqlite3_prepare_v2(sqlite3_db, sql->getCString(), -1, &stmt, &tail);
    if (rc != SQLITE_OK) {
//        fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(sqlite3_db));
    }
    
    rc = sqlite3_step(stmt);

    cocos2d::CCArray *columns = this->readTableColumnNamesAndTypes(tableName);

    int column_count = columns->count();
    
    while (rc == SQLITE_ROW) {
        cocos2d::CCDictionary *col_data = cocos2d::CCDictionary::create();
        for (int i = 0; i < column_count; i++) {
            cocos2d::CCDictionary *dict = (cocos2d::CCDictionary *)columns->objectAtIndex(i);

            cocos2d::CCString *column_name = (cocos2d::CCString *)dict->objectForKey("name");
            const char *s_name = column_name->getCString();
            std::string key(s_name);
            cocos2d::CCString *type = (cocos2d::CCString *)dict->objectForKey("type");
            const char *s_type = type->getCString();
        
            cocos2d::CCObject *co_var;
            
            if (0 == strcmp(s_type, "integer")) {
                int i_var = sqlite3_column_int(stmt, i);
                co_var = cocos2d::CCInteger::create(i_var);
            } else if (0 == strcmp(s_type, "text")) {
                char *s_var = (char *)sqlite3_column_text(stmt, i);
                if (s_var == NULL) {
                    s_var = '\0';
                }
                co_var = cocos2d::CCString::create(std::string(s_var));
            } else if (0 == strcmp(s_type, "blob")) {
                const void *s_var = (const void *)sqlite3_column_blob(stmt, i);

                int len = sqlite3_column_bytes(stmt, i);
                if (len == 0) {
                    co_var = cocos2d::CCString::create("");
                } else {
                    char *temp_var = (char *)malloc(len+1);
                    temp_var[len] = '\0';
                    if (len > 0)
                    {
                        memcpy(temp_var, s_var, len);
                    }
                    std::cout << temp_var << std::endl;
                    
                    co_var = cocos2d::CCString::createWithData((const unsigned char *)temp_var, len);
                    free(temp_var);
                }
            } else if (0 == strcmp(s_type, "real")) {
                float f_var = sqlite3_column_double(stmt, i);
                co_var = cocos2d::CCFloat::create(f_var);
            }
            col_data->setObject(co_var, key);
        }
        retArr->addObject(col_data);
        
        rc = sqlite3_step(stmt);
    }
    sqlite3_finalize(stmt);
    
    return retArr;
}

int Sqlite3Utils::insert(const char *tableName, cocos2d::CCDictionary *inserData)
{
    cocos2d::CCArray *columns = this->readTableColumnNamesAndTypes(tableName);
    
    // sql语句
    cocos2d::CCString *sql;
    
    std::string value_format;
    
    for (int i = 0; i < columns->count(); i++) {
        if (0 == i) {
            value_format.append("?");
        } else {
            value_format.append(",?");
        }
    }
    
    sql = cocos2d::CCString::createWithFormat("insert into %s values(%s)", tableName, value_format.c_str());
    
    std::cout << "要插入的字符串sql格式为" << sql->getCString() << std::endl;
    
    sqlite3_stmt *stmt;
    const char *tail;
    // 准备
    int rc = sqlite3_prepare_v2(sqlite3_db, sql->getCString(), -1, &stmt, &tail);

    // 要插入的数据的keys
    cocos2d::CCArray *insert_keys = inserData->allKeys();
    
    // 绑定变量
    for (int i = 0; i < columns->count(); i++) {
        cocos2d::CCDictionary *columnDict = (cocos2d::CCDictionary *)columns->objectAtIndex(i);
        cocos2d::CCString *column_name = (cocos2d::CCString *)columnDict->objectForKey("name");
        for (int j = 0; j < insert_keys->count(); j++) {
            cocos2d::CCString *key = (cocos2d::CCString *)insert_keys->objectAtIndex(j);
            if (key->isEqual(column_name)) {
                cocos2d::CCString *type = (cocos2d::CCString *)columnDict->objectForKey("type");
                const char *s_type = type->getCString();
                
                if (0 == strcmp(s_type, "integer")) {
                    cocos2d::CCInteger *i_var = (cocos2d::CCInteger *)inserData->objectForKey(std::string(key->getCString()));
                    sqlite3_bind_int(stmt, i+1, i_var->getValue());
                } else if (0 == strcmp(s_type, "text")) {
                    cocos2d::CCString *s_var = (cocos2d::CCString *)inserData->objectForKey(std::string(key->getCString()));
                    const char *char_var = s_var->getCString();
                    sqlite3_bind_text(stmt, i+1, char_var, strlen(char_var), SQLITE_STATIC);
                } else if (0 == strcmp(s_type, "blob")) {
                    cocos2d::CCString *s_var = (cocos2d::CCString *)inserData->objectForKey(std::string(key->getCString()));
                    const char *char_var = s_var->getCString();
                    int lenth = strlen(char_var);
                    void *void_var = malloc(lenth);
                    memcpy(void_var, char_var, lenth);
                    sqlite3_bind_blob(stmt, i+1, void_var, lenth, SQLITE_TRANSIENT);
                } else if (0 == strcmp(s_type, "real")) {
                    cocos2d::CCFloat *f_var = (cocos2d::CCFloat *)inserData->objectForKey(std::string(key->getCString()));
                    float float_var = f_var->getValue();
                    sqlite3_bind_double(stmt, i+1, float_var);
                }
            }
        }
    }
    // 执行
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_OK) {
//        fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(sqlite3_db));
    }
    sqlite3_finalize(stmt);
    
    return rc;
}

int Sqlite3Utils::update(const char *tableName, cocos2d::CCDictionary *updateData, const char *condition)
{
//    cocos2d::CCArray *columns = this->readTableColumnNamesAndTypes(tableName);
    cocos2d::CCArray *columnNames = updateData->allKeys();
    // sql语句
    cocos2d::CCString *sql;
    
    std::string value_format;
    
    for (int i = 0; i < columnNames->count(); i++) {
        cocos2d::CCString *update_name = (cocos2d::CCString *)columnNames->objectAtIndex(i);
        cocos2d::CCObject *column_value = updateData->objectForKey(update_name->getCString());
        cocos2d::CCPrettyPrinter printer;
        column_value->acceptVisitor(printer);
        if (0 != i) {
            value_format.append(",");
        }
        value_format.append(update_name->getCString());
        value_format.append("=");
        value_format.append("'");
        value_format.append(printer.getResult());
        value_format.append("'");
    }

    sql = cocos2d::CCString::createWithFormat("update %s set %s where %s", tableName, value_format.c_str(), condition);
    
    std::cout << "要更新的字符串sql格式为 " << sql->getCString() << std::endl;
    
    sqlite3_stmt *stmt;
    const char *tail;
    // 准备
    int rc = sqlite3_prepare_v2(sqlite3_db, sql->getCString(), -1, &stmt, &tail);
    
    // 执行
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_OK) {
//        fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(sqlite3_db));
    }
    sqlite3_finalize(stmt);
    
    return rc;
}

int Sqlite3Utils::remove(const char *tableName, const char *condition)
{
    // sql语句
    cocos2d::CCString *sql;
    
    std::string value_format(condition);
    sql = cocos2d::CCString::createWithFormat("delete from %s where %s", tableName, condition);
    
    std::cout << "要删除的字符串sql格式为 " << sql->getCString() << std::endl;
    
    sqlite3_stmt *stmt;
    const char *tail;
    // 准备
    int rc = sqlite3_prepare_v2(sqlite3_db, sql->getCString(), -1, &stmt, &tail);
    
    // 执行
    rc = sqlite3_step(stmt);
    if (rc != SQLITE_OK) {
//        fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(sqlite3_db));
    }
    sqlite3_finalize(stmt);
    
    return rc;
}

cocos2d::CCArray* Sqlite3Utils::readTableColumnNamesAndTypes(const char *tableName)
{
    cocos2d::CCArray *retArr = cocos2d::CCArray::create();
    
    cocos2d::CCString *sql = cocos2d::CCString::createWithFormat("select * from %s", tableName);
    
    sqlite3_stmt *stmt;
    const char *tail;
    
    bool rc = sqlite3_prepare_v2(this->sqlite3_db, sql->getCString(), -1, &stmt, &tail);
    
    if (rc != SQLITE_OK) {
//        fprintf(stderr, "SQL error: %s\n", sqlite3_errmsg(sqlite3_db));
    }
    
    int column_count = sqlite3_column_count(stmt);
    
    for (int i = 0; i < column_count; i++) {
        cocos2d::CCDictionary *dict = cocos2d::CCDictionary::create();
        
        const char *column_name = sqlite3_column_name(stmt, i);
        cocos2d::CCString *name = cocos2d::CCString::createWithFormat("%s", column_name);
        
        const char * column_type = sqlite3_column_decltype(stmt, i);
        cocos2d::CCString *type = cocos2d::CCString::create(column_type);
        
        dict->setObject(name, "name");
        dict->setObject(type, "type");
        
        retArr->addObject(dict);
    }
    
    /*
    for (int i = 0; i < retArr->count(); i++) {
        cocos2d::CCDictionary *dict = (cocos2d::CCDictionary *)retArr->objectAtIndex(i);
        
        cocos2d::CCString *name = (cocos2d::CCString *)dict->objectForKey("name");
        cocos2d::CCString *type = (cocos2d::CCString *)dict->objectForKey("type");
        
//        printf("column_name column(%d) = %s type = %s\n", i, name->getCString(), type->getCString());
    }*/
    
    sqlite3_finalize(stmt);
    
//    std::cout << "retArr count = " << retArr->count() << std::endl;
    
    return retArr;
}
